package com.yourwork.common.jpa;

import java.util.List;

import javax.persistence.Table;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.yourwork.model.StaffManagement;
import com.yourwork.view.model.EmployeeManageSum;
@Repository
@Table(name="employeeManage")
@Qualifier("EmployeeManageRepositoey")
public interface StaffManagementRepositoey extends JpaRepository<StaffManagement, Integer> {
//分页查询在职员工 或离职员工
List<StaffManagement> findByStatus(String status,Pageable pageable);
//查询详细员工信息
@Query(value="SELECT em FROM  StaffManagement em WHERE em.id=:id")
List<StaffManagement> queryDetailed(@Param("id")int id);
//多字段模糊查询
@Query(value=("SELECT * FROM  staff_management em WHERE"+
" concat(IFNULL(name,''),IFNULL(phone,''),IFNULL(number,''),IFNULL(sex,'')) "+
"like %:conditions% "),nativeQuery = true)
List<StaffManagement> queryConditions(@Param("conditions")String conditions);
//员工编号查询记录
StaffManagement findByNumber(String number);
@Query("SELECT NEW com.yourwork.view.model.EmployeeManageSum(sr.employeeManage,"
		+ "sr.employeeManage.employeeType,sr,sum(sr.payavle),count(sr.number),"
		+ "(sum(sr.payavle)*sr.employeeManage.employeeType.commissionRate+"
		+ "sr.employeeManage.employeeType.basicSalary)) FROM  Consumption sr WHERE sr.number = :number and MONTH(sr.date) = :dateMonth")
public EmployeeManageSum querySalary(@Param("number") String number,@Param("dateMonth") int dateMonth);
List<StaffManagement> findByStatus(String status);
}
